package pl.ppsoft.fixlang;

import java.text.DateFormat;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import pl.ppsoft.fixlang.dto.LessonDTO;
import pl.ppsoft.fixlang.dto.RestLessonDTO;
import pl.ppsoft.fixlang.dto.RestWordDTO;
import pl.ppsoft.fixlang.dto.WordDTO;
import pl.ppsoft.fixlang.enums.LearnModeEnum;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDoneException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class DbAdapter {

	private static final String LESSONS_TABLE = "lessons";
    public static final String LESSON_ROWID = "_id";
    public static final String LESSON_SERVER_ID = "server_id";
    public static final String LESSON_NAME = "name";
    public static final String LESSON_WORD_COUNT = "word_count";    
    public static final String LESSON_QUESTIONING = "questioning";
    public static final String LESSON_ANSWERING = "answering";
    public static final String LESSON_WRITING = "writing";
    public static final String LESSON_LISTENING = "listening";
    public static final String LESSON_PRONOUNCE = "pronounce";
    public static final String LESSON_LESSONSTAMP = "lessonstamp";
    public static final String LESSON_WORDSTAMP = "wordstamp";
    public static final String LESSON_STATUS = "status";    
    
    public static final int LESSON_STATUS_DELETED = 0;
    public static final int LESSON_STATUS_DO_ARCHIVE = 1;
    public static final int LESSON_STATUS_ARCHIVED = 2;
    public static final int LESSON_STATUS_OK = 3;
    public static final int LESSON_STATUS_NEW = 4;
    public static final int LESSON_STATUS_MODIFIED = 5;
    public static final int LESSON_STATUS_SYNC_MODIFIED_WORDS = 6;
    public static final int LESSON_STATUS_SYNC_NEW_WORDS = 7;
    public static final int LESSON_STATUS_SYNC_MODIFIED_ARCHIVE = 8;
    public static final int LESSON_STATUS_SYNC_NEW_ARCHIVE = 9;
    public static final int LESSON_STATUS_SYNC_ARCHIVE_OK = 10;
    
	private static final String WORDS_TABLE = "words";
    public static final String WORD_ROWID = "_id";
    public static final String WORD_SERVER_ID = "server_id";
    public static final String WORD_LEARN_TIME = "learn_time";
    public static final String WORD_QUESTION = "question";
    public static final String WORD_ANSWER = "answer";
    public static final String WORD_EXAMPLE = "example";
    public static final String WORD_LESSON_ID = "lesson_id";
    public static final String WORD_QUESTIONING = "questioning";
    public static final String WORD_ANSWERING = "answering";
    public static final String WORD_WRITING = "writing";
    public static final String WORD_LISTENING = "listening";
    public static final String WORD_PRONOUNCE = "pronounce";
    public static final String WORD_WORDSTAMP = "wordstamp";
    public static final String WORD_STATUS = "status";

    public static final int WORD_STATUS_DELETED = 0;
    public static final int WORD_STATUS_OK = 1;
    public static final int WORD_STATUS_NEW = 2;
    public static final int WORD_STATUS_MODIFIED = 3;    
    
	private static final String HISTORY_TABLE = "history";
    public static final String HISTORY_ROWID = "_id";
    public static final String HISTORY_TIME = "time";
    public static final String HISTORY_TOTAL = "total";
    public static final String HISTORY_PASSED = "passed";
    public static final String HISTORY_QUESTIONING = "questioning";
    public static final String HISTORY_ANSWERING = "answering";
    public static final String HISTORY_LISTENING = "listening";
    public static final String HISTORY_WRITING = "writing";

	private static final String HISTLESSON_TABLE = "histlesson";
	public static final String HISTLESSON_ROWID = "_id";
	public static final String HISTLESSON_HISTORY_ID = "history_id";
    public static final String HISTLESSON_LESSON_ID = "lesson_id";
    public static final String HISTLESSON_TOTAL = "total";
    
    public final DateFormat timestampFormat = new SimpleDateFormat( "yyMMddHHmmss" );
    
    private DatabaseHelper dbHelper;
    private SQLiteDatabase db;
    
    private static final String DATABASE_NAME = "FixLangDB";
    private static final int DATABASE_VERSION = 1;

    private final Context context;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        	String sql = MessageFormat.format("CREATE TABLE {0} ({1} integer primary key, {2} integer, {3} text not null COLLATE NOCASE, {4} integer, {5} integer, {6} integer );", 
        			             			  LESSONS_TABLE, LESSON_ROWID, LESSON_SERVER_ID, LESSON_NAME, LESSON_LESSONSTAMP, LESSON_STATUS, LESSON_WORDSTAMP );        	
            db.execSQL(sql);
        	
            sql = MessageFormat.format("CREATE TABLE {0} ({1} integer primary key, {2} integer, {3} integer not null, {4} text not null COLLATE NOCASE, {5} text not null COLLATE NOCASE, {6} text COLLATE NOCASE, {7} integer, {8} integer, {9} integer, ", 
       			  						WORDS_TABLE, WORD_ROWID, WORD_SERVER_ID, WORD_LESSON_ID, WORD_QUESTION, WORD_ANSWER, WORD_EXAMPLE, WORD_QUESTIONING, WORD_ANSWERING, WORD_WRITING );        	            
            sql += MessageFormat.format("{0} integer, {1} integer, {2} integer, {3} integer, {4} integer );", WORD_LISTENING, WORD_PRONOUNCE, WORD_LEARN_TIME, WORD_WORDSTAMP, WORD_STATUS );        	            
            db.execSQL(sql);

        	sql = MessageFormat.format("CREATE TABLE {0} ({1} integer primary key, {2} integer,{3} integer, {4} integer, {5} integer, {6} integer, {7} integer, {8} integer );", 
       			  						HISTORY_TABLE, HISTORY_ROWID, HISTORY_TIME, HISTORY_TOTAL, HISTORY_PASSED, HISTORY_QUESTIONING, HISTORY_ANSWERING, HISTORY_LISTENING, HISTORY_WRITING );        	
        	db.execSQL(sql);
        
        	sql = MessageFormat.format("CREATE TABLE {0} ({1} integer primary key, {2} integer, {3} integer, {4} integer);", 
 									    HISTLESSON_TABLE, HISTLESSON_ROWID, HISTLESSON_HISTORY_ID, HISTLESSON_LESSON_ID, HISTLESSON_TOTAL );        	
        	db.execSQL(sql);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        	
        	db.execSQL("DROP TABLE IF EXISTS " + LESSONS_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + WORDS_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + HISTORY_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + HISTLESSON_TABLE);

            onCreate(db);
        }
    }

	public void recreate() {
		dbHelper.onUpgrade(db, 0, 0);
	}

    public DbAdapter(Context context) {
        this.context = context;
    }

    public DbAdapter open() throws SQLException {
        dbHelper = new DatabaseHelper(context);
        db = dbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        dbHelper.close();
    }

	public void beginTransaction() {
		db.beginTransaction();
	}

	public void endTransaction() {
		db.endTransaction();
	}

	public void setTransactionSuccessful() {
		db.setTransactionSuccessful();
	}
	
/****************************** LESSONS *************************************/
    
	
    public void addLesson(String name) {    	    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_SERVER_ID, 0);
        initialValues.put(LESSON_NAME, name);        
        initialValues.put(LESSON_STATUS, LESSON_STATUS_NEW);
        initialValues.put(LESSON_LESSONSTAMP, System.currentTimeMillis() );
        initialValues.put(LESSON_WORDSTAMP, System.currentTimeMillis() );

        db.insertOrThrow(LESSONS_TABLE, null, initialValues);
    }
        
    public void deleteLesson(long id) {
    	SQLiteStatement sql = db.compileStatement("select server_id from lessons where _id = ?");
    	sql.bindLong(1, id);
    	try {
    		long remoteId = sql.simpleQueryForLong();
    		if ( remoteId > 0 ) {
    			ContentValues initialValues = new ContentValues();
    			initialValues.put(LESSON_STATUS, LESSON_STATUS_DELETED );
    			db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);
    		} else {
                db.delete(WORDS_TABLE, WORD_LESSON_ID + "=" + id, null);
                db.delete(LESSONS_TABLE, LESSON_ROWID + "=" + id, null);    			
    		}
    	} catch (SQLiteDoneException e) {
    	}    	    	
    }
    
	private static String fetchAllLessonsSql = "select l._id, l.name, " +
		"(select count(*) from words w where w.lesson_id = l._id and w.status > 0 ) word_count, " +
		"(select count(*) from words w where w.lesson_id = l._id and w.questioning = 0 and w.status > 0 ) questioning, " +
		"(select count(*) from words w where w.lesson_id = l._id and w.answering = 0 and w.status > 0 ) answering, " +
		"(select count(*) from words w where w.lesson_id = l._id and w.writing = 0 and w.status > 0 ) writing, " +
		"(select count(*) from words w where w.lesson_id = l._id and w.listening = 0 and w.status > 0 ) listening " +
		"from lessons l where l.status > 2";
    public Cursor fetchChartLessons(String sort, boolean desc) {
    	String orderby = "";
    	if ( sort != null ) {
    		orderby = " order by " + sort;
    		if ( desc ) {
    			orderby+=" desc";
    		}
    	}
    	
    	return db.rawQuery( fetchAllLessonsSql + orderby, null);
    }
    
	private static String fetchManageLessonsSql = "select l._id, l.name, l.status, " +
		"(select count(*) from words w where w.lesson_id = l._id and w.status > 0) word_count " +
		"from lessons l where l.status > 0";
    public Cursor fetchManageLessons(String sort, boolean desc) {
    	String orderby = "";
    	if ( sort != null ) {
    		orderby = " order by " + sort;
    		if ( desc ) {
    			orderby+=" desc";
    		}
    	}
    	
    	return db.rawQuery( fetchManageLessonsSql + orderby, null);
    }

    public LessonDTO fetchLesson(long id) throws SQLException {
        Cursor c = db.query(true, LESSONS_TABLE, 
        				           new String[] {LESSON_NAME},
        				           LESSON_ROWID + "=" + id, null, null, null, null, null);
        try {
        	c.moveToFirst();
        	LessonDTO lesson = new LessonDTO();
        	lesson.setId( id );
        	lesson.setName( c.getString( c.getColumnIndexOrThrow(LESSON_NAME) ) );
        	return lesson;
        } finally {
        	c.close();
        }
    }

    public boolean updateLesson(long id, String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_NAME, name );
        initialValues.put(LESSON_STATUS, LESSON_STATUS_MODIFIED );
        initialValues.put(LESSON_LESSONSTAMP, System.currentTimeMillis() );
    	return db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null) > 0;
    }

    public void lessonWordModified(long id) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_WORDSTAMP, 1 );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);
    }

	public void archiveLesson(long id) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_STATUS, LESSON_STATUS_DO_ARCHIVE );
        initialValues.put(LESSON_LESSONSTAMP, System.currentTimeMillis() );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);				
	}

	public void unarchiveLesson(long id) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_STATUS, LESSON_STATUS_OK );
        initialValues.put(LESSON_LESSONSTAMP, 0 );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);				
	}
	
/****************************** WORDS *************************************/
    
    public Cursor fetchManageWords(long lessonId, String sort, boolean desc) {
    	String orderby = "";
    	if ( sort != null ) {
    		orderby = sort;
    		if ( desc ) {
    			orderby+=" desc";
    		}
    	}
    	
        return db.query(WORDS_TABLE, 
		           new String[] {WORD_ROWID, WORD_QUESTION, WORD_ANSWER, WORD_EXAMPLE},
		           "lesson_id = ? and status > 0", new String[] { String.valueOf( lessonId ) }, null, null, orderby);
    }
            
	private static String fetchGlobalWordsSql = "select count(*) from words, lessons " +
		"where words.lesson_id = lessons._id and lessons.status > 2 and words.status > 0";
    public int fetchGlobalWords(String field) {		    	
    	String sql = fetchGlobalWordsSql;
		if ( field != null ) {
			sql += " and words." + field + " = 0";
		}
		
    	SQLiteStatement stmnt = db.compileStatement(sql);
    	return (int)stmnt.simpleQueryForLong();
	}

    public List<Long> loadWordIds(List<Long> lessonIds, int wordsCount, LearnModeEnum mode, List<LearnModeEnum> randomModes, boolean repeat) {
    	String value = repeat?"1":"0";
		
		StringBuffer sb = new StringBuffer();
    	if ( !lessonIds.isEmpty() ) {
	    	sb.append( WORD_LESSON_ID );
	    	sb.append( " in (" );
	    	sb.append( lessonIds.get(0) );    	
	    	for (int i = 1; i < lessonIds.size(); i++) {
	    		Long id = lessonIds.get(i);
	    		if ( id > 0 ) {
	    			sb.append( "," );
	    			sb.append( id );	    		
	    		}
			}
	    	sb.append( ") AND " );
    	}

    	switch (mode) {
		case QUESTIONING:
			sb.append( WORD_QUESTIONING );
			sb.append( " = " );
			sb.append( value );			
			break;
		case ANSWERING:
			sb.append( WORD_ANSWERING );
			sb.append( " = " );
			sb.append( value );			
			break;
		case WRITING:
			sb.append( WORD_WRITING );
			sb.append( " = " );
			sb.append( value );			
			break;
		case LISTENING:
			sb.append( WORD_LISTENING );
			sb.append( " = " );
			sb.append( value );			
			break;
		case PRONOUNCE:
			sb.append( WORD_PRONOUNCE );
			sb.append( " = " );
			sb.append( value );			
			break;
		case RANDOM:
			sb.append( " (" );
			for (int i = 0; i < randomModes.size(); i++ ) {
				sb.append( this.enumToColumn( randomModes.get(i) ) );
				sb.append( " = " );
				sb.append( value );			
				if ( i < randomModes.size() - 1 ) {
					sb.append( " OR " );
				}
			}
			sb.append( ") " );
			break;
    	}    	    	    	
    	
    	Cursor c;
    	if ( wordsCount >= 0 ) {
    		sb.append( " AND words.status > 0 AND lessons._id = words.lesson_id AND lessons.status > 2" );
    		c = db.query(true, WORDS_TABLE + ", " + LESSONS_TABLE, new String[] {WORDS_TABLE+"."+WORD_ROWID}, sb.toString(), null, null, null, WORD_LEARN_TIME, String.valueOf(wordsCount));
    	} else {
    		sb.append( " AND status > 0" );
    		c = db.query(true, WORDS_TABLE, new String[] {WORD_ROWID}, sb.toString(), null, null, null, null, null);
    	}
        try {
        	List<Long> result = new ArrayList<Long>();
        	if ( c.moveToFirst() ) {
        		do {
        			result.add( c.getLong( 0 ) );
        		} while ( c.moveToNext() );        		
        	}
        	return result;
        } finally {
        	c.close();
        }                
	}
    
	private String enumToColumn( LearnModeEnum mode ) {
		switch (mode) {
			case ANSWERING:			
				return WORD_ANSWERING;
			case LISTENING:			
				return WORD_LISTENING;
			case PRONOUNCE:			
				return WORD_PRONOUNCE;
			case QUESTIONING:			
				return WORD_QUESTIONING;
			case WRITING:			
				return WORD_WRITING;
			default:
				return WORD_ANSWERING;
		}
	}
	
    public WordDTO fetchWord(long id) throws SQLException {
        Cursor c = db.query(true, WORDS_TABLE, 
        				           new String[] {WORD_QUESTION, WORD_ANSWER, WORD_EXAMPLE, WORD_QUESTIONING, WORD_ANSWERING, WORD_WRITING, WORD_LISTENING, WORD_LESSON_ID},
        				           WORD_ROWID + "=" + id, null, null, null, null, null);
        try {
        	c.moveToFirst();
        	WordDTO word = new WordDTO();
        	word.setId( id );
        	word.setQuestion( c.getString( c.getColumnIndexOrThrow(WORD_QUESTION) ) );
        	word.setAnswer( c.getString( c.getColumnIndexOrThrow(WORD_ANSWER) ) );
        	word.setExample( c.getString( c.getColumnIndexOrThrow(WORD_EXAMPLE) ) );
        	word.setQuestioning( c.getInt( c.getColumnIndexOrThrow(WORD_QUESTIONING) ) );
        	word.setAnswering( c.getInt( c.getColumnIndexOrThrow(WORD_ANSWERING) ) );
        	word.setWriting( c.getInt( c.getColumnIndexOrThrow(WORD_WRITING) ) );
        	word.setListening( c.getInt( c.getColumnIndexOrThrow(WORD_LISTENING) ) );
        	word.setLesson_id( c.getLong( c.getColumnIndexOrThrow(WORD_LESSON_ID) ) );
        	return word;
        } finally {
        	c.close();
        }
    }
    
    public boolean resetWords(Long lessonId) {
    	List<Long> list = new ArrayList<Long>();
    	list.add( lessonId );
    	return resetWords(list);
    }

    public boolean resetWords(List<Long> lessonIds) {
    	StringBuffer sb = new StringBuffer();
    	sb.append( WORD_LESSON_ID );
    	sb.append( " in (" );
    	sb.append( lessonIds.get(0) );    	
    	for (int i = 1; i < lessonIds.size(); i++) {
    		Long id = lessonIds.get(i);
    		if ( id > 0 ) {
    			sb.append( "," );
    			sb.append( id );	    		
    		}
		}
    	sb.append( ")" );
    	
    	ContentValues args = new ContentValues();
		args.put(WORD_QUESTIONING, 0);
		args.put(WORD_ANSWERING, 0);
		args.put(WORD_WRITING, 0);
		args.put(WORD_LISTENING, 0);
		args.put(WORD_PRONOUNCE, 0);
		
        return db.update(WORDS_TABLE, args, sb.toString(), null) > 0;
    }
    
    public void wordLearned(long wordId, long lessonId, boolean learned, LearnModeEnum mode, long historyId, int touched, int passed) {
        db.beginTransaction();
        try {
	    	ContentValues args = new ContentValues();
	        args.put(WORD_LEARN_TIME, System.currentTimeMillis());
	        switch (mode) {
			case QUESTIONING:
				args.put(WORD_QUESTIONING, learned?1:0);
				break;
			case ANSWERING:
				args.put(WORD_ANSWERING, learned?1:0);
				break;
			case WRITING:
				args.put(WORD_WRITING, learned?1:0);
				break;
			case LISTENING:
				args.put(WORD_LISTENING, learned?1:0);
				break;
			case PRONOUNCE:
				args.put(WORD_PRONOUNCE, learned?1:0);
				break;
	        }
	
	        db.update(WORDS_TABLE, args, WORD_ROWID + "=" + wordId, null);
	        this.updateHistory(historyId, lessonId, touched, passed);

	        db.setTransactionSuccessful();
        } finally {
        	db.endTransaction();
        }
    }

	public void addWord(long lessonId, String qvalue, String avalue, String evalue) {
		db.beginTransaction();
		try {		
	        ContentValues initialValues = new ContentValues();
	        initialValues.put(WORD_SERVER_ID, 0);
	        initialValues.put(WORD_LESSON_ID, lessonId);
	        initialValues.put(WORD_QUESTION, qvalue);
	        initialValues.put(WORD_ANSWER, avalue );
	        initialValues.put(WORD_EXAMPLE, evalue );
	        initialValues.put(WORD_LEARN_TIME, System.currentTimeMillis());
	        initialValues.put(WORD_QUESTIONING, 0);
	        initialValues.put(WORD_ANSWERING, 0);
	        initialValues.put(WORD_WRITING, 0);
	        initialValues.put(WORD_LISTENING, 0);
	        initialValues.put(WORD_PRONOUNCE, 0);
	        initialValues.put(WORD_STATUS, WORD_STATUS_NEW);
	        initialValues.put(WORD_WORDSTAMP, System.currentTimeMillis());
	
	        db.insertOrThrow(WORDS_TABLE, null, initialValues);		
	        this.lessonWordModified( lessonId );        
	        
	        db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
    
	public void updateWord(long id, long lessonId, String qvalue, String avalue, String evalue) {
        db.beginTransaction();
		try {
			ContentValues initialValues = new ContentValues();
	        initialValues.put(WORD_QUESTION, qvalue);
	        initialValues.put(WORD_ANSWER, avalue );
	        initialValues.put(WORD_EXAMPLE, evalue );
	        initialValues.put(WORD_WORDSTAMP, System.currentTimeMillis());
	        initialValues.put(WORD_STATUS, WORD_STATUS_MODIFIED);
	    	db.update(WORDS_TABLE, initialValues, WORD_ROWID + "=" + id, null);
	    	
	        this.lessonWordModified( lessonId );        
	        db.setTransactionSuccessful();
		} finally {
        	db.endTransaction();
        }
	}

	public void deleteWord(long id, long lessonId) {
    	SQLiteStatement sql = db.compileStatement("select server_id from words where _id = ?");
    	sql.bindLong(1, id);
    	try {
    		long remoteId = sql.simpleQueryForLong();
    		if ( remoteId > 0 ) {
    			db.beginTransaction();
    			try {
	    			ContentValues initialValues = new ContentValues();
	    			initialValues.put(WORD_STATUS, WORD_STATUS_DELETED );
	    			db.update(WORDS_TABLE, initialValues, WORD_ROWID + "=" + id, null);
    			
	    	        this.lessonWordModified( lessonId );        
	    			db.setTransactionSuccessful();
    			} finally {
    				db.endTransaction();
    			}
    		} else {
    			db.delete(WORDS_TABLE, WORD_ROWID + "=" + id, null);
    		}
    	} catch (SQLiteDoneException e) {
    	}    	    	
	}

/****************************** HISTORY *************************************/

	public long addHistory(LearnModeEnum mode, List<LearnModeEnum> randomModes) {
        db.beginTransaction();
        try {		
			ContentValues initialValues = new ContentValues();
	        initialValues.put(HISTORY_TIME, System.currentTimeMillis());
	        initialValues.put(HISTORY_QUESTIONING, 0);
	        initialValues.put(HISTORY_ANSWERING, 0);
	        initialValues.put(HISTORY_LISTENING, 0);
	        initialValues.put(HISTORY_WRITING, 0);
	
	        if ( mode == LearnModeEnum.RANDOM ) {
	        	for (LearnModeEnum learnModeEnum : randomModes) {
	        		initialValues.put(this.methodColumn(learnModeEnum), 1);					
				}
	        } else {
	        	initialValues.put(this.methodColumn(mode), 1);
	        }
	        
	        long historyId = db.insertOrThrow(HISTORY_TABLE, null, initialValues);
	        
	        initialValues = new ContentValues();
	        initialValues.put(HISTLESSON_HISTORY_ID, historyId );
	        	        
	        db.setTransactionSuccessful();	        
	        return historyId;
        } finally {
        	db.endTransaction();
        }
	}

	private String methodColumn(LearnModeEnum mode) {		
		switch (mode) {
		case QUESTIONING:			
			return HISTORY_QUESTIONING;
		case ANSWERING:			
			return HISTORY_ANSWERING;
		case LISTENING:			
			return HISTORY_LISTENING;
		case WRITING:			
			return HISTORY_WRITING;
		default:
			return HISTORY_QUESTIONING;
		}
	}
	
	public void updateHistory(long historyId, long lessonId, int total, int passed) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(HISTORY_TOTAL, total);
        initialValues.put(HISTORY_PASSED, passed);
    	db.update(HISTORY_TABLE, initialValues, HISTORY_ROWID + "=" + historyId, null);
    	
    	SQLiteStatement sql = db.compileStatement("select total from histlesson where history_id = ? and lesson_id = ?" );
    	sql.bindLong(1, historyId);
    	sql.bindLong(2, lessonId);    	
    	try {
    		long counter = sql.simpleQueryForLong() + 1;
        	initialValues = new ContentValues();
            initialValues.put(HISTLESSON_TOTAL, counter);
        	db.update(HISTLESSON_TABLE, initialValues, "history_id=? and lesson_id=?", new String[] {String.valueOf(historyId), String.valueOf(lessonId)});    	
    	} catch (SQLiteDoneException e) {
        	initialValues = new ContentValues();
            initialValues.put(HISTLESSON_HISTORY_ID, historyId);
            initialValues.put(HISTLESSON_LESSON_ID, lessonId);
            initialValues.put(HISTLESSON_TOTAL, 1);
	        db.insertOrThrow(HISTLESSON_TABLE, null, initialValues);
    	}    	    	
	}

    public Cursor fetchHistory() {
        return db.query(HISTORY_TABLE, 
		           new String[] {HISTORY_ROWID, HISTORY_TOTAL, HISTORY_PASSED, HISTORY_TIME, HISTORY_QUESTIONING, HISTORY_ANSWERING, HISTORY_LISTENING, HISTORY_WRITING},null, null, null, null, HISTORY_ROWID + " DESC");
    }

	private static String fetchHistoryLessonsSql = "select histlesson._id, lessons.name, histlesson.total " +
												   "from histlesson, lessons where histlesson.lesson_id = lessons._id and histlesson.history_id = ?";
    public Cursor fetchHistoryLessons(long historyId) {
    	return db.rawQuery( fetchHistoryLessonsSql, new String[] { String.valueOf(historyId) });
    }

/****************************** REST *************************************/

    private Long parseTimestamp( String timestamp ) {
    	try {
			return timestampFormat.parse( timestamp ).getTime();
		} catch (ParseException e) {
			return 0L;
		}
    }
    
    public long addRestLesson(RestLessonDTO lesson) {    	    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_SERVER_ID, lesson.getR());
        initialValues.put(LESSON_NAME, lesson.getN());
        initialValues.put(LESSON_STATUS, LESSON_STATUS_OK);
        initialValues.put(LESSON_LESSONSTAMP, this.parseTimestamp( lesson.getT() ) );
        initialValues.put(LESSON_WORDSTAMP, this.parseTimestamp( lesson.getW() ) );

        return db.insertOrThrow(LESSONS_TABLE, null, initialValues);
    }

    public void updateRestLesson(long id, String name, String lessonstamp) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_NAME, name );
        initialValues.put(LESSON_STATUS, LESSON_STATUS_OK );
        initialValues.put(LESSON_LESSONSTAMP, this.parseTimestamp( lessonstamp ) );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);
    }
    
    public void updateRestLessonStatus(long id, int status) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_STATUS, status );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);
    }

    public void updateRestLessonServerId(long id, long serverId) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_SERVER_ID, serverId );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);
    }

    public void updateRestLessonWordStamp(long id, String wordstamp) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(LESSON_WORDSTAMP, this.parseTimestamp( wordstamp ) );
    	db.update(LESSONS_TABLE, initialValues, LESSON_ROWID + "=" + id, null);
    }

    public void deleteRestLesson(long id) {
        db.beginTransaction();
        try {
        	db.delete(WORDS_TABLE, WORD_LESSON_ID + "=" + id, null);
	    	db.delete(LESSONS_TABLE, LESSON_ROWID + "=" + id, null);
	    	db.setTransactionSuccessful();
        } finally {
        	db.endTransaction();
        }
    }

    public void addRestWord(RestWordDTO word, Long lessonId) throws Exception {
        ContentValues initialValues = new ContentValues();
        initialValues.put(WORD_LESSON_ID, lessonId);
        initialValues.put(WORD_SERVER_ID, word.getR());        
        initialValues.put(WORD_QUESTION, word.getQ());
        initialValues.put(WORD_ANSWER, word.getA());
        initialValues.put(WORD_EXAMPLE, word.getE());
        initialValues.put(WORD_WORDSTAMP, this.parseTimestamp( word.getT() ));
        initialValues.put(WORD_STATUS, WORD_STATUS_OK);
        initialValues.put(WORD_LEARN_TIME, System.currentTimeMillis());
        initialValues.put(WORD_QUESTIONING, 0);
        initialValues.put(WORD_ANSWERING, 0);
        initialValues.put(WORD_WRITING, 0);
        initialValues.put(WORD_LISTENING, 0);
        initialValues.put(WORD_PRONOUNCE, 0);
        
        db.insertOrThrow(WORDS_TABLE, null, initialValues);
    }

    public void deleteRestWords(long lessonId) {
    	db.delete(WORDS_TABLE, WORD_LESSON_ID + "=" + lessonId, null);
    }

    public void deleteRestWord(long id) {
    	db.delete(WORDS_TABLE, WORD_ROWID + "=" + id, null);
    }

    public void updateRestWord(long id, String question, String answer, String example, String wordstamp) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(WORD_QUESTION, question );
        initialValues.put(WORD_ANSWER, answer );
        initialValues.put(WORD_EXAMPLE, example );
        initialValues.put(WORD_STATUS, LESSON_STATUS_OK );
        initialValues.put(WORD_WORDSTAMP, this.parseTimestamp( wordstamp ) );
    	db.update(WORDS_TABLE, initialValues, WORD_ROWID + "=" + id, null);
    }
        
    public void updateRestWordStatus(long id, long serverId, int status) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(WORD_STATUS, status );
        initialValues.put(WORD_SERVER_ID, serverId );
    	db.update(WORDS_TABLE, initialValues, WORD_ROWID + "=" + id, null);
    }

    public List<RestLessonDTO> restSyncLessons() {
    	List<RestLessonDTO> lessons = new ArrayList<RestLessonDTO>();
    	
    	Cursor c = db.query(LESSONS_TABLE, new String[] {LESSON_ROWID, LESSON_SERVER_ID, LESSON_NAME, LESSON_STATUS, LESSON_LESSONSTAMP, LESSON_WORDSTAMP}, LESSON_STATUS + "<>" + LESSON_STATUS_ARCHIVED, null, null, null, null);
    	int idIndex = c.getColumnIndexOrThrow( LESSON_ROWID );
    	int serverIdIndex = c.getColumnIndexOrThrow( LESSON_SERVER_ID );
    	int nameIndex = c.getColumnIndexOrThrow( LESSON_NAME );
    	int statusIndex = c.getColumnIndexOrThrow( LESSON_STATUS );
    	int lessonstampIndex = c.getColumnIndexOrThrow( LESSON_LESSONSTAMP );
    	int wordstampIndex = c.getColumnIndexOrThrow( LESSON_WORDSTAMP );
    	
    	RestLessonDTO lesson;    	
        try {
        	if ( c.moveToFirst() ) {
        		do {
        			lesson = new RestLessonDTO();        			
        			lesson.setI( c.getLong( idIndex ) );
        			lesson.setR( c.getLong( serverIdIndex ) );
        			lesson.setT( timestampFormat.format( c.getLong( lessonstampIndex ) ) );
        			lesson.setW( timestampFormat.format( c.getLong( wordstampIndex ) ) );
        			lesson.setS( c.getInt( statusIndex ) );
        			if ( lesson.getR() == 0 || lesson.getS() == LESSON_STATUS_MODIFIED || lesson.getS() == LESSON_STATUS_DO_ARCHIVE ) {
        				lesson.setN( c.getString( nameIndex ) );
        			}

        			lessons.add( lesson );
        		} while ( c.moveToNext() );        		
        	}
        	return lessons;
        } finally {
        	c.close();
        }                    	
    }

    public List<RestWordDTO> restSyncWords(long lessonId) {
    	List<RestWordDTO> words = new ArrayList<RestWordDTO>();
    	
    	Cursor c = db.query(WORDS_TABLE, new String[] {WORD_ROWID, WORD_SERVER_ID, WORD_QUESTION, WORD_ANSWER, WORD_EXAMPLE, WORD_STATUS, WORD_WORDSTAMP}, WORD_LESSON_ID +"="+lessonId , null, null, null, null);
    	int idIndex = c.getColumnIndexOrThrow( WORD_ROWID );
    	int serverIdIndex = c.getColumnIndexOrThrow( WORD_SERVER_ID );
    	int questionIndex = c.getColumnIndexOrThrow( WORD_QUESTION );
    	int answerIndex = c.getColumnIndexOrThrow( WORD_ANSWER );
    	int exampleIndex = c.getColumnIndexOrThrow( WORD_EXAMPLE );
    	int statusIndex = c.getColumnIndexOrThrow( WORD_STATUS );
    	int wordstampIndex = c.getColumnIndexOrThrow( WORD_WORDSTAMP );
    	
    	RestWordDTO word;    	
        try {
        	if ( c.moveToFirst() ) {
        		do {
        			word = new RestWordDTO();        			
        			word.setI( c.getLong( idIndex ) );
        			word.setR( c.getLong( serverIdIndex ) );
        			word.setT( timestampFormat.format( c.getLong( wordstampIndex ) ) );
        			word.setS( c.getInt( statusIndex ) );
        			if ( word.getR() == 0 || word.getS() == WORD_STATUS_MODIFIED ) {
        				word.setQ( c.getString( questionIndex ) );
        				word.setA( c.getString( answerIndex ) );
        				word.setE( c.getString( exampleIndex ) );
        			}

        			words.add( word );
        		} while ( c.moveToNext() );        		
        	}
        	return words;
        } finally {
        	c.close();
        }                    	
    }

    public void doLessonArchive(long lessonId) {
        db.beginTransaction();
        try {
        	this.deleteRestWords(lessonId);
	    	this.updateRestLessonStatus(lessonId, LESSON_STATUS_ARCHIVED);
	    	db.setTransactionSuccessful();
        } finally {
        	db.endTransaction();
        }
    }

}
